package com.uwo.house.util;

import com.uwo.house.beans.request.MaterialListReq;
import com.uwo.house.dao.MaterialListDao;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.stereotype.Service;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RequestParam;

import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.net.URL;
import java.net.URLConnection;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.List;

/**
 * Created by PHS on 2017/9/25.
 */
@Service
//@Controller
//@RequestMapping("/importExcel")
public class ImportExcel {
    @Autowired
    private  MaterialListDao materialListDao;

    /**
     * 读取Excel
     *
     */
    //@RequestMapping(value = "/importInfo" ,method = RequestMethod.POST)
    public int loadExcel(String filePath,Integer planId) throws IOException {
        //InputStream in = null;
        //filePath = "http://120.27.151.238:8080/uwo-house/attachments/150831867696253104.xlsx";
        //planId = 84;
        URL url = new URL(filePath);
        URLConnection conn = url.openConnection();
        HSSFWorkbook hWorkbook = null;
        XSSFWorkbook xWorkbook = null;
        Sheet sheet = null;
        int result = 0;
        List<MaterialListReq> materialList = null;

        if (null == filePath) {
            throw new RuntimeException("没有此文档!");
        } else if (filePath.toLowerCase().endsWith("xls")) {
            //in = new FileInputStream(filePath);
            hWorkbook = new HSSFWorkbook(conn.getInputStream());
            for (int i = 0; i < hWorkbook.getNumberOfSheets(); i++) {
                sheet = hWorkbook.getSheetAt(i);
                materialList = getSheetData(sheet,planId);
            }
            result = materialListDao.addMeasureList(materialList);
        } else if (filePath.toLowerCase().endsWith("xlsx")) {
            //in = new FileInputStream(filePath);
            xWorkbook = new XSSFWorkbook(conn.getInputStream());
            for (int i = 0; i < xWorkbook.getNumberOfSheets(); i++) {
                sheet = xWorkbook.getSheetAt(i);
                materialList = getSheetData(sheet,planId);
                result = materialListDao.addMeasureList(materialList);
            }
        } else {
            throw new RuntimeException("文档格式不正确!");
        }
        return result;
    }

    private List<MaterialListReq> getSheetData(Sheet sheet,Integer planId) {
        int rowNum = sheet.getLastRowNum();
        Row rowFirst = sheet.getRow(1);
        int cellNum = rowFirst.getLastCellNum();
        Row row = null;
        Cell cell = null;
        List<MaterialListReq> materialList = new ArrayList<MaterialListReq>();
        MaterialListReq material = null;

        for (int i = 0; i <= rowNum; i++) {
            material = new MaterialListReq();
            row = sheet.getRow(i+2);
            if (null != row) {
                for (int j = 0; j < cellNum; j++) {
                    material.setPlanId(planId);
                    if (j == 0) {
                        cell = row.getCell(j);
                        if (null != cell && !"".equals(cell)) {
                            String materialType = cell.toString();
                            //String[] arge = materialType.split(".0");
                            //material.setMaterialType(arge[0]);
                            material.setMaterialType(materialType);
                        }
                    }
                    if (j == 1) {
                        cell = row.getCell(j);
                        if (null != cell && !"".equals(cell)) {
                            String supplierName = cell.toString();
                            //String[] arge = supplierName.split(".0");
                            //material.setSupplierName(arge[0]);
                            material.setSupplierName(supplierName);
                        }
                    }
                    if (j == 2) {
                        cell = row.getCell(j);
                        if (null != cell && !"".equals(cell)) {
                            String supplierNo = cell.toString();
                            //String[] arge = supplierNo.split(".0");
                            //material.setSupplierNo(arge[0]);
                            material.setSupplierNo(supplierNo);
                        }
                    }
                    if (j == 3) {
                        cell = row.getCell(j);
                        if (null != cell && !"".equals(cell)) {
                            String materialNo = cell.toString();
                            //String[] arge = materialNo.split(".0");
                            //material.setMaterialNo(arge[0]);
                            material.setMaterialNo(materialNo);
                        }
                    }

                    if (j == 4) {
                        cell = row.getCell(j);
                        if (null != cell && !"".equals(cell)) {
                            String materialName = cell.toString();
                            //String[] arge = materialName.split(".0");
                            //material.setMaterialName(arge[0]);
                            material.setMaterialName(materialName);
                        }
                    }

                    if (j == 5) {
                        cell = row.getCell(j);
                        if (null != cell && !"".equals(cell)) {
                            /*double orderId = cell.getNumericCellValue();
                            DecimalFormat decimalFormat = new DecimalFormat("##########################.#####");
                            material.setOrderId(decimalFormat.format(orderId));*/
                            String orderId = cell.toString();
                            //String[] arge = orderId.split(".0");
                            material.setOrderId(orderId);
                        }
                    }

                    if (j == 6) {
                        cell = row.getCell(j);
                        if (null != cell && !"".equals(cell)) {
                            String color = cell.toString();
                            //String[] arge = color.split(".0");
                            //material.setColor(arge[0]);
                            material.setColor(color);
                        }
                    }

                    if (j == 7) {
                        cell = row.getCell(j);
                        if (null != cell && !"".equals(cell)) {
                            String size = cell.toString();
                            //String[] arge = size.split(".0");
                            //material.setSize(arge[0]);
                            material.setSize(size);
                        }
                    }

                    if (j == 8) {
                        cell = row.getCell(j);
                        if (null != cell && !"".equals(cell)) {
                            String sourceArea = cell.toString();
                            //String[] arge = sourceArea.split(".0");
                            //material.setSourceArea(arge[0]);
                            material.setSourceArea(sourceArea);
                        }
                    }

                    if (j == 9) {
                        cell = row.getCell(j);
                        if (null != cell && !"".equals(cell)) {
                            String brand = cell.toString();
                            //String[] arge = brand.split(".0");
                            //material.setBrand(arge[0]);
                            material.setBrand(brand);
                        }
                    }

                    if (j == 10) {
                        cell = row.getCell(j);
                        if (null != cell && !"".equals(cell)) {
                            String quality = cell.toString();
                            //String[] arge = quality.split(".0");
                            //material.setQuality(arge[0]);
                            material.setQuality(quality);
                        }
                    }

                    if (j == 11) {
                        cell = row.getCell(j);
                        if (null != cell && !"".equals(cell)) {
                            material.setUnitPrice(cell.getNumericCellValue());
                        }
                    }

                    if (j == 12) {
                        cell = row.getCell(j);
                        if (null != cell && !"".equals(cell)) {
                            material.setNumber(cell.getNumericCellValue());
                        }
                    }

                    if (j == 13) {
                        cell = row.getCell(j);
                        if (null != cell && !"".equals(cell)) {
                            material.setTotalPrice(cell.getNumericCellValue());
                        }
                    }
                }
                materialList.add(material);
            }
        }
        return materialList;
    }

}
